package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;

import ca.ualberta.cs.BibTex.Author;
import ca.ualberta.cs.BibTex.BibTex;
import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

public class ResourceManagement extends EntityRelManagement {
	
	static AuthoringManagement authoringMgmt = new AuthoringManagement();
	static TagsManagement tagsMgmt = new TagsManagement();
	static BibTexManagement bibtexMgmt = new BibTexManagement();
	static ReadingManagement readingMgmt = new ReadingManagement();
	static WatchListManagement watchlistMgmt = new WatchListManagement();
	
	public ResourceManagement(){	
		mainTableName = "Resource";
	}
	
	public void initialize(Statement statement) throws SQLException{
		
		try{
			statement.executeUpdate("DROP TABLE Resource");
		}catch(Exception e){}
		
		bibtexMgmt.initialize(statement);
				
		statement.executeUpdate("CREATE TABLE Resource ( " +
				"ID INTEGER GENERATED ALWAYS AS " +
					"IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, " +
				"url VARCHAR(300) NOT NULL, " +
				"bibtexId INTEGER, " +
				"documentDigest VARCHAR(300), " +
				"documentTextDigest VARCHAR(300), " +
				"CONSTRAINT id_uniq PRIMARY KEY(ID)," +
				"CONSTRAINT url_uniq UNIQUE(url), " +
				"CONSTRAINT FK_bibtexid FOREIGN KEY (bibtexId) " +
                	"REFERENCES BibTex (ID) ON DELETE CASCADE " +				
		") IN CLRSPACE INDEX IN CLRSPACE"); 
		numOfCols = 5;
		//statement.executeUpdate("create unique index u_resource on Resource(url,bibtexId)"); //url is unique leads to uniqueness of (url,bibtexId)
	}
	
	   /**
	    * When bib is not provided
	    * @param url
	    * @param con
	    * @return -1: does not exists, -2: an exception, other: resourceId
	    */
	   public static int resourceExists(String url, Connection con){
		   
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   
		   int id = -1; //does not exist
		   
		   try{
				Statement statement = con.createStatement();
				ResultSet res = statement.executeQuery("SELECT ID FROM Resource WHERE url = '"+ url +"'" ) ;
				if(res.next())
					id = res.getInt("ID");
				else
					id = -1;

			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
				
		   } catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
				return -2;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return -2;
			} 
		    return id;
	   }
	   

	   
	   /**
	    * checks whether a resource with exactly this bib and url already exists
	    * @param bib
	    * @param con
	    * @return -1: does not exists, -2: an exception, resourceId: exists
	    */
	   public static int resourceExists(Integer bibtexId, String url, Connection con){
		   
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }		   
		   
		   int id = -1; //does not exist
		   	
		   try{
			   Statement statement = con.createStatement();
			   //now find resourceId
			   ResultSet res = statement.executeQuery("SELECT ID FROM Resource WHERE bibtexId = " + bibtexId +
			   		(url!=null? " AND url = '"+url+"'":"") );
			   
			   if( res.next() ){
				   id = res.getInt("ID");
			   }else
				   id = -1;
			   
			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
		   
		   } catch (SQLException e) {			   
			   DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
				return -2;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return -2;
			} 
		   
		   return id;
	   }
	   
	   
	   /**
	    * @param bib
	    * @param connection
	    * @return id of the added resource, or -1 in case of exception
	    */
	   public int addResourceWithBibtex(BibTex bib, String url, Connection con){
		   
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   
		   int resourceId = -1;
		   try{
			   Statement statement = con.createStatement();

			   Integer bibtexId = bibtexMgmt.insertNewBibTex(bib, statement);
			   
			   String keys = "(bibtexId" + (url!=null? ",url ":"") + ")";
			   String values = "("+bibtexId+ (url!=null? ",'"+url+"'":"") + ")";
			   
			   try{
				   resourceId = resourceExists(url, con);
				   if(resourceId < 0){
					   statement.executeUpdate("INSERT INTO Resource "+keys+" VALUES " +values , Statement.RETURN_GENERATED_KEYS);
					   ResultSet resId = statement.getGeneratedKeys();
					   resId.next();
					   resourceId = resId.getInt(1);
				   }
				   else{
					   statement.executeUpdate("UPDATE Resource SET bibtexId = " +bibtexId +" WHERE ID = "+resourceId);
				   }
				   
			   }catch(SQLException e){ //unique constraint disturbed
				   if(e.getErrorCode() != -803 ) {
					   DB2SqlException.closeConnection(con, innerConnection);
					   throw e;
				   }
				   else{
					  ResultSet re = statement.executeQuery("SELECT ID FROM Resource WHERE url = '"+url+"'");
					  if(re.next())
						  resourceId = re.getInt("ID");
				   }
			   }
			   
			   //add authoring and authors (new bibtex and new resource)
//			   int firstAuthor = authoringMgmt.addAuthoringAndAuthors(resourceId, bib.getAuthors(), statement);
			   authoringMgmt.addAuthoring(bibtexId, bib.getAuthorNamesList(), statement);
			
//			   if(firstAuthor != -1)
				   tagsMgmt.addKeywords(bib.getKeywords(), bibtexId, statement);
			   //add tagging
//				   tagsMgmt.addTaggings(bib.getKeywords(), resourceId, firstAuthor, 0, null, statement);
			   	
			   
			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
			   
		   }catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
				return -1;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return -1;
			} 
		   return resourceId;
	   }
	   
	   public int addResourceWithBibtexId(Integer bibtexId, BibTex bib, String url, Connection con){
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   
		   int resourceId = -1;
		   try{
			   Statement statement = con.createStatement();
			   
			   String keys = "(bibtexId" + (url!=null? ",url ":"") + ")";
			   String values = "("+bibtexId+ (url!=null? ",'"+url+"'":"") + ")";
			   
			   try{
				   resourceId = resourceExists(url, con);
				   if(resourceId < 0){
					   statement.executeUpdate("INSERT INTO Resource "+keys+" VALUES " +values , Statement.RETURN_GENERATED_KEYS);
					   ResultSet resId = statement.getGeneratedKeys();
					   resId.next();
					   resourceId = resId.getInt(1);
				   }
				   else{
					   statement.executeUpdate("UPDATE Resource SET bibtexId = " +bibtexId +" WHERE ID = "+resourceId);
				   }
				   
			   }catch(SQLException e){ //unique constraint disturbed
				   if(e.getErrorCode() != -803 ){
					   DB2SqlException.closeConnection(con, innerConnection);
					   throw e;
				   }
				   else{
					  ResultSet re = statement.executeQuery("SELECT ID FROM Resource WHERE url = '"+url+"'");
					  if(re.next())
						  resourceId = re.getInt("ID");
				   }
			   }
			   
			   //add authoring to this new resource, but not authors since bibtex is already added
//			   int firstAuthor = authoringMgmt.addAuthoring(resourceId, bibtexId, bib.getAuthors(), statement);
			   authoringMgmt.addAuthoring(bibtexId, bib.getAuthorNamesList(), statement);
			   
//			   if(firstAuthor != -1)
				   tagsMgmt.addKeywords(bib.getKeywords(), bibtexId, statement);
			   //add tagging to this new resource
//				   tagsMgmt.addTaggings(bib.getKeywords(), resourceId, firstAuthor, 0, null, statement);
			   
			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
			   
		   }catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
				return -1;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return -1;
			} 
		   return resourceId;
	   }
	   
	   /**
	    * When bib is not provided
	    * @param url
	    * @param connection
	    * @return id of the added resource, or -1 in case of exception
	    */
	   public static int addResource(String url, String docDigest, String textDigest, Connection con){
		   
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   
		   int id = -1;
		   try{
			   Statement statement = con.createStatement();

			   String keys = "(url" + (docDigest!=null?",documentDigest":"") + (textDigest!=null?",documentTextDigest":"") + ")";
			   String vals = "('"+url + (docDigest!=null?"','"+docDigest:"") + (textDigest!=null?"','"+textDigest:"") + "')";
			   
			   statement.executeUpdate("INSERT INTO Resource "+keys+" VALUES "+vals , Statement.RETURN_GENERATED_KEYS);
			   ResultSet resId = statement.getGeneratedKeys();
			   resId.next();
			   id = resId.getInt(1);
			   
			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
			   
		   }catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
				return -1;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return -1;
			} 
		   return id;
	   }
	   
	   public void updateResourceDigest(Integer resourceId, String docDigest, String textDigest, Connection con){
		   if(docDigest==null && textDigest==null)
			   return;
		   
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   
		   try{
			   Statement statement = con.createStatement();
			   
			   statement.executeUpdate("UPDATE Resource SET (documentDigest,documentTextDigest) " +
			   		"= ('" + docDigest +"','"+textDigest+"')" +
			   		"WHERE ID = " + resourceId); 
			   
			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
			   
		   }catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
			} 
	   }
	   

	   
	   /**
	    * 
	    * @param bib
	    * @param resourceId
	    * @return first author
	    */
	   /*private int addAuthorsOfResource(BibTex bib, Integer resourceId){
		   Connection con = DatabaseController.connectToDB();
		   int firstAuthor = -1;
		   try{
			   Statement statement = con.createStatement();
			   
			   firstAuthor = authoringMgmt.addAuthoringAndAuthors(resourceId, bib.getAuthors(), statement);
			   
			   statement.close();
			   
			   con.commit();
			   con.close();				   
			   
		   }catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e);						
			} catch (Exception e) {			
				e.printStackTrace();				
			}
			return firstAuthor;
	   }*/
	   
	   
	   
	   /*public void addResourceTaggings(BibTex bib, Integer resourceId, Integer firstAuthorId){
		   
		   Connection con = DatabaseController.connectToDB();
		   try{
			   Statement statement = con.createStatement();
			   if(firstAuthorId == null || firstAuthorId.equals(new Integer(-1)))
				   firstAuthorId = authoringMgmt.findThisAuthorsIdByResource(resourceId, bib.getAuthors().get(0), statement);
			   
//			   tagsMgmt.addTaggings(bib.getKeywords(), resourceId, firstAuthorId, 0, null, statement);
			   tagsMgmt.addKeywords(bib.getKeywords(), bibtexId, statement);
			   
			   statement.close();
			   
			   con.commit();
			   con.close();				   
			   
		   }catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e);						
			} catch (Exception e) {			
				e.printStackTrace();				
			}
		
	   }*/
	   
	   public int addOrUpdateResource(BibTex bib, HashMap<String,String> paperInfo, String url){
		   
		   int resourceId = -1;
		   
		   if(paperInfo != null){
			   bib = new BibTex(null);
			   if(paperInfo.containsKey("title"))
				   bib.setTitle(paperInfo.get("title"));			   
			   if(paperInfo.containsKey("keywords"))
				   bib.setKeywords(paperInfo.get("keywords"));
			   if(paperInfo.containsKey("fullAuthorInfo"))
				   bib.setFullAuthorsInfo(paperInfo.get("fullAuthorInfo")); //it sets the authors
			   
			   bib = new BibTex(bib.toString());
			   
			   if(paperInfo.containsKey("abstract")) //not set in 'toString'
				   bib.setAbstract(paperInfo.get("abstract"));
			   
			   if(bib.getTitle() == null || bib.getTitle().length() == 0)
				   bib = null;
		   }
		   if(bib != null ){
			   
			   int bibtexId = bibtexMgmt.bibtexExists(bib, null);
			   if(bibtexId < 0){ //bibtex and hence resource (combination of bibtex and url) not exist				
				   resourceId = addResourceWithBibtex(bib, url, null);
				   /*Integer firstAuthorId = addAuthorsOfResource(bib, resourceId);
				   addResourceTaggings(bib, resourceId, firstAuthorId);*/
			   }
			   else{ //bibtex exists, not sure about resource 
				   BibTex updatedBib = bibtexMgmt.updateBibtex(bibtexId, bib, null);
				   resourceId = resourceExists(bibtexId, url, null);
				   if( resourceId < 0){				
					   resourceId = addResourceWithBibtexId(bibtexId, updatedBib, url, null);
				   }
				   else if(!updatedBib.keywordsToString().equals(bib.keywordsToString())) //if resourceExists, bibtex may have been updated with new keywords
					   //inside TagsManagement it is checked that a tag is not inserted more than once for a person for a resource for a page
					   //here we could skip checking old bib and updated bib, but it helps reducing time
//					   addResourceTaggings(updatedBib, resourceId, null);
					   tagsMgmt.addKeywords(updatedBib.getKeywords(), bibtexId, null);
			   }
			   
		   }
		   else{
			   resourceId = resourceExists(url, null);
				if( resourceId < 0)
					resourceId = addResource(url, null, null, null);
		   }
		   
			return resourceId;
	   }
	   
	   public AuthoringManagement getAuthoringManagement(){
		   return authoringMgmt;
	   }

		public TagsManagement getTagsManagement() {
			return tagsMgmt;
		}
	   
	   public BibTexManagement getBibTexManagement(){
		   return bibtexMgmt;
	   }
	   
	   public ReadingManagement getReadingManagement(){
		   return readingMgmt;
	   }
	   
	   public WatchListManagement getWatchListManagement(){
		   return watchlistMgmt;
	   }
	   		
}
